﻿CREATE TABLE [CA].[GoodsVsContragents] (
    [Id]               INT           NOT NULL,
    [GoodsId]          INT           NOT NULL,
    [ContragentId]     INT           NOT NULL,
    [GoodsCode]        INT           NOT NULL,
    [GoodsName]        VARCHAR (150) NOT NULL,
    [CountryName]      VARCHAR (20)  NULL,
    [ManufacturerName] VARCHAR (70)  NULL,
    [Note]             VARCHAR (250) NULL,
    [MD]               DATETIME      CONSTRAINT [DF__GoodsVsContr__MD__5B78929E] DEFAULT (getdate()) NOT NULL,
    [MW]               VARCHAR (50)  CONSTRAINT [DF__GoodsVsContr__MW__5C6CB6D7] DEFAULT (suser_sname()) NOT NULL,
    [ToDelete]         BIT           DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_GoodsVsContragents] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (FILLFACTOR = 80),
    CONSTRAINT [FK_GoodsVsContragents_Contragents] FOREIGN KEY ([ContragentId]) REFERENCES [CA].[Contragents] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION,
    CONSTRAINT [FK_GoodsVsContragents_Goods] FOREIGN KEY ([GoodsId]) REFERENCES [CA].[Goods] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
);


GO
CREATE NONCLUSTERED INDEX [IX_GoodsVsContragents_GoodsCode]
    ON [CA].[GoodsVsContragents]([ContragentId] ASC, [GoodsCode] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_GoodsVsContragents_GoodsId]
    ON [CA].[GoodsVsContragents]([ContragentId] ASC, [GoodsId] ASC) WITH (FILLFACTOR = 80);


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Код препарата у контрагента', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'GoodsVsContragents', @level2type = N'COLUMN', @level2name = N'GoodsCode';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Наименование препарата у контрагента', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'GoodsVsContragents', @level2type = N'COLUMN', @level2name = N'GoodsName';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Наименование страны производства  препарата у контрагента', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'GoodsVsContragents', @level2type = N'COLUMN', @level2name = N'CountryName';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Наименование производителя препарата у контрагента', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'GoodsVsContragents', @level2type = N'COLUMN', @level2name = N'ManufacturerName';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Признак того, что запись нужно удалить из БД всех подразделений', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'GoodsVsContragents', @level2type = N'COLUMN', @level2name = N'ToDelete';

